#!/usr/bin/python
# coding:utf-8

import pymysql
import os
from dbpool import create_cursor


def test_db(count):
    with create_cursor() as (conn, cursor):
        # SQL 查询语句
        sql = "SELECT * FROM `user` ORDER BY `id` DESC "
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            if count is None:
                result = cursor.fetchone()
                result.pop('pwd_hash')
            elif count == 'all':
                result = cursor.fetchall()
            elif count.isdigit():
                result = cursor.fetchmany(int(count))
            else:
                result = cursor.fetchone()

            return result
        except pymysql.MySQLError as e:
            return dict(error=e.args[0], message=e.args[1])
        except Exception as e:
            return dict(error=500, message=str(e))


def write_db():
    with create_cursor() as (conn, cursor):
        try:
            # 开始事务
            conn.begin()
            cursor.execute("INSERT INTO `user` (`id`, `name`, `nickname`) VALUES (%s, %s, %s)",
                           (None, 'YCB1', os.getpid()))
            conn.commit()

            cursor.execute("SELECT * FROM `user` ORDER BY `id` DESC")
            result = cursor.fetchone()
            return result
        except pymysql.MySQLError as e:
            conn.rollback()
            return dict(error=e.args[0], message=e.args[1])
        except Exception as e:
            conn.rollback()
            return dict(error=500, message=str(e))


def update_db():
    print('pid %d' % os.getpid())
    with create_cursor() as (conn, cursor):
        try:
            id = 1
            # 开始事务
            conn.begin()
            cursor.execute("UPDATE `user` SET `pid`=`pid`+1 WHERE id=%s", id)
            conn.commit()

            cursor.execute("SELECT * FROM `user` WHERE id=%s", id)
            result = cursor.fetchone()
            return result
        except pymysql.MySQLError as e:
            conn.rollback()
            return dict(error=e.args[0], message=e.args[1])
        except Exception as e:
            conn.rollback()
            return dict(error=500, message=str(e))